# _*_coding=utf-8 _*_
"""
名称：mission_03_2
版本：v1.0
功能：
使用 sqlalchemy ORM 方式创建如下表，使用 PyMySQL 对该表写入 3 条测试数据，并读取:
1.用户 id、用户名、年龄、生日、性别、学历、字段创建时间、字段更新时间;
2.将 ORM、插入、查询语句作为作业内容提交
时间：2020-12-12
作者：长毛怪^^
"""

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime
from sqlalchemy import DateTime

Base = declarative_base()


class User_table(Base):
    __tablename__ = 'user_meg'
    user_id = Column(Integer(), primary_key=True)
    user_name = Column(String(50), index=True)
    user_age = Column(Integer())
    user_btday = Column(String(20))
    user_gender = Column(String(10))
    user_edt = Column(String(50))
    created_on = Column(DateTime(), default=datetime.now)
    updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)

    def __repr__(self):
        return "User_table(user_id='{self.user_id}', user_name={self.user_name}, user_btday={self.user_btday})"\
            .format(self=self)


dburl = "mysql+pymysql://testuser:Testpass!123@192.168.1.168:3306/testdb?charset=utf8mb4"
engine = create_engine(dburl, echo=True, encoding="utf-8")
Base.metadata.create_all(engine)

SessionClass = sessionmaker(bind=engine)
session = SessionClass()


def add_data():
    user_demo = User_table(user_id=1,
                           user_name='王小红',
                           user_age=22,
                           user_btday='1998-12-12',
                           user_gender='female',
                           user_edt='硕士研究生'
                           )
    user_demo2 = User_table(user_id=2,
                            user_name='李月月',
                            user_age=33,
                            user_btday='1987-12-12',
                            user_gender='male',
                            user_edt='大学本科'
                            )

    user_demo3 = User_table(user_id=3,
                            user_name='陈大锤',
                            user_age=18,
                            user_btday='2002-12-12',
                            user_gender='male',
                            user_edt='大学专科'
                            )

    session.add(user_demo)
    session.add(user_demo2)
    session.add(user_demo3)

    session.commit()


def query_data():
    print([result for result in session.query(User_table)])

    session.commit()


if __name__ == '__main__':
    # add_data()
    query_data()
